USE [MASTER]
-- Kills the database connections which are running
DECLARE @i INT
DECLARE @error INT
DECLARE @x INT
declare @command nvarchar(max)

IF object_id('tempdb..#LIVE_SPIDS') IS NOT NULL
BEGIN
   DROP TABLE #LIVE_SPIDS
END


CREATE TABLE #LIVE_SPIDS (nid INT IDENTITY(1,1), spid INT NOT NULL)
SET @error = 0
SET @i = 1

TRUNCATE TABLE #LIVE_SPIDS

INSERT INTO #LIVE_SPIDS
SELECT 
	spid
FROM Master.dbo.SYSPROCESSES
WHERE dbid IN (SELECT dbid FROM Master.dbo.SYSDATABASES WHERE name = 'AmazonDW')
	AND spid <> @@SPID

SET @x = (SELECT MIN(nid) FROM #LIVE_SPIDS)

WHILE @x <= (SELECT MAX(nid) FROM #LIVE_SPIDS)
BEGIN

	SET @command = 'KILL ' + CONVERT(VARCHAR, (SELECT spid FROM #LIVE_SPIDS WHERE nid = @x))

	EXECUTE sp_executesql @command--, NO OUTPUT
	SET @error = @error + @@ERROR

	SET @x = @x + 1

END

GO

---DROP The Database if it exists
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'AmazonDW')
 	DROP DATABASE AmazonDW
GO
CREATE DATABASE AmazonDW
GO

USE AmazonDW
GO


IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[AuditPkgExecution]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [AuditPkgExecution] 
GO

/* Create table AuditPkgExecution */
CREATE TABLE [AuditPkgExecution] (
   [PkgExecKey]  int IDENTITY  NOT NULL
,  [PkgName]  varchar(50)   NULL
,  [PkgGUID]  uniqueidentifier   NULL
,  [PkgVersionGUID]  uniqueidentifier   NULL
,  [PkgVersionMajor]  smallint   NULL
,  [PkgVersionMinor]  smallint   NULL
,  [ExecStartDT]  datetime   NULL
,  [ExecStopDT]  datetime   NULL
,  [SuccessfulProcessingInd]  char(1)   NULL
,  [ParentPkgExecKey]  int   NULL
, CONSTRAINT [PK_AuditPkgExecution] PRIMARY KEY CLUSTERED 
( [PkgExecKey] )
) ON [PRIMARY]
GO


SET IDENTITY_INSERT AuditPkgExecution ON
GO
INSERT INTO AuditPkgExecution (PkgExecKey, PkgName, PkgGUID, PkgVersionGUID, PkgVersionMajor, PkgVersionMinor, ExecStartDT, ExecStopDT, SuccessfulProcessingInd, ParentPkgExecKey)
VALUES (-1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
GO
SET IDENTITY_INSERT AuditPkgExecution OFF
GO


/* Drop table AuditTableProcessing */
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[AuditTableProcessing]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [AuditTableProcessing] 
GO

/* Create table AuditTableProcessing */
CREATE TABLE [AuditTableProcessing] (
   [TableProcessKey]  int IDENTITY  NOT NULL
,  [PkgExecKey]  int   NOT NULL
,  [TableName]  varchar(50)   NULL
,  [ExtractRowCnt]  int   NULL
,  [ExtractCheckValue1]  float   NULL
,  [ExtractCheckValue2]  float   NULL
,  [InsertStdRowCnt]  int   NULL
,  [InsertStdCheckValue1]  float   NULL
,  [InsertStdCheckValue2]  float   NULL
,  [InsertNonstdRowCnt]  int   NULL
,  [InsertNonstdCheckValue1]  float   NULL
,  [InsertNonstdCheckValue2]  float   NULL
,  [UpdateRowCnt]  int   NULL
,  [ErrorRowCnt]  int   NULL
,  [TableInitialRowCnt]  int   NULL
,  [TableFinalRowCnt]  int   NULL
,  [SuccessfulProcessingInd]  char(1)   NULL
, CONSTRAINT [PK_AuditTableProcessing] PRIMARY KEY CLUSTERED 
( [TableProcessKey] )
) ON [PRIMARY]
GO


SET IDENTITY_INSERT AuditTableProcessing ON
GO
INSERT INTO AuditTableProcessing (TableProcessKey, PkgExecKey, TableName, ExtractRowCnt, ExtractCheckValue1, ExtractCheckValue2, InsertStdRowCnt, InsertStdCheckValue1, InsertStdCheckValue2, InsertNonstdRowCnt, InsertNonstdCheckValue1, InsertNonstdCheckValue2, UpdateRowCnt, ErrorRowCnt, TableInitialRowCnt, TableFinalRowCnt, SuccessfulProcessingInd)
VALUES (-1, -1, 'Unknown', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
GO
SET IDENTITY_INSERT AuditTableProcessing OFF
GO



/* Drop table DimAudit */
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DimAudit]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [DimAudit] 
GO

/* Create table DimAudit */
CREATE TABLE [DimAudit] (
   [AuditKey]  int IDENTITY  NOT NULL
,  [TableProcessKey]  int   NOT NULL
,  [BranchName]  varchar(50)   NOT NULL
,  [BranchRowCnt]  int   NULL
,  [ProcessingSummaryGroup]  varchar(25)   NOT NULL
, CONSTRAINT [PK_DimAudit] PRIMARY KEY CLUSTERED 
( [AuditKey] )
) ON [PRIMARY]
GO


SET IDENTITY_INSERT DimAudit ON
GO
INSERT INTO DimAudit (AuditKey, TableProcessKey, BranchName, BranchRowCnt, ProcessingSummaryGroup)
VALUES (-1, -1, 'Test', NULL, 'Test')
GO
SET IDENTITY_INSERT DimAudit OFF
GO


/* Drop table FactListItem */
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[FactListItem]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [FactListItem] 
GO

/* Create table FactListItem */
CREATE TABLE [FactListItem] (
   [ListKey]  int   NOT NULL
,  [BookKey]  int   NOT NULL
,  [ListItemId]  char(15)   NOT NULL
,  [DateAddedKey]  int   NOT NULL
,  [QuantityDesired]  int  DEFAULT 0 NOT NULL
,  [QuantityReceived]  int  DEFAULT 0 NOT NULL
,  [RowIsCurrent]  char(1)   NOT NULL
,  [RowStartDate]  int  DEFAULT -1 NOT NULL
,  [RowEndDate]  int  DEFAULT -1 NULL
,  [AuditKey]  int   NULL
) ON [PRIMARY]
GO

/* Drop table DimList */
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DimList]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [DimList] 
GO

/* Create table DimList */
CREATE TABLE [DimList] (
   [ListKey]  int IDENTITY  NOT NULL
,  [ListId]  char(14)   NULL
,  [ListName]  varchar(70)   NULL
,  [ListUrl]  varchar(255)   NULL
,  [ListType]  char(9)   NULL
,  [RowIsCurrent]  char(1)   NULL
,  [RowStartDate]  datetime   NULL
,  [RowEndDate]  datetime  DEFAULT '9999-12-31' NULL
,  [RowChangeReason]  varchar(200)   NULL
,  [AuditKey]  int   NULL
, CONSTRAINT [PK_DimList] PRIMARY KEY CLUSTERED 
( [ListKey] )
) ON [PRIMARY]
GO


SET IDENTITY_INSERT DimList ON
GO
INSERT INTO DimList (ListKey, ListId, ListName, ListUrl, ListType, RowIsCurrent, RowStartDate, RowEndDate, RowChangeReason, AuditKey)
VALUES (-1, NULL, NULL, NULL, NULL, 'Y', NULL, '9999-12-31', 'N/A', -1)
GO
SET IDENTITY_INSERT DimList OFF
GO


/* Drop table FactList */
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[FactList]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [FactList] 
GO

/* Create table FactList */
CREATE TABLE [FactList] (
   [ListKey]  int   NOT NULL
,  [CustomerKey]  int   NOT NULL
,  [CreatedKey]  int   NOT NULL
,  [UpdatedKey]  int   NOT NULL
,  [TotalItems]  int   NOT NULL
,  [AuditKey]  int   NULL
) ON [PRIMARY]
GO


--Sellers and them Offers

/* Drop table FactSellerFeedback */
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[FactSellerFeedback]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [FactSellerFeedback] 
GO

/* Create table FactSellerFeedback */
CREATE TABLE [FactSellerFeedback] (
   [SellerFeedbackKey]  int   NOT NULL
,  [SellerKey]  int   NOT NULL
,  [RatedByKey]  int   NOT NULL
,  [CreatedDateKey]  int   NOT NULL
,  [Rating ]  tinyint   NOT NULL
,  [AuditKey]  int   NULL
) ON [PRIMARY]
GO



/* Drop table DimSeller */
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DimSeller]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [DimSeller] 
GO

/* Create table DimSeller */
CREATE TABLE [DimSeller] (
   [SellerKey]  int IDENTITY  NOT NULL
,  [SellerId]  char(14)   NULL
,  [NickName]  varchar(128)   NULL
,  [Name]  varchar(128)   NULL
,  [GlancePage]  varchar(255)   NULL
,  [LegalName]  varchar(128)   NULL
,  [AuditKey]  int   NULL
, CONSTRAINT [PK_DimSeller] PRIMARY KEY CLUSTERED 
( [SellerKey] )
) ON [PRIMARY]
GO


SET IDENTITY_INSERT DimSeller ON
GO
INSERT INTO DimSeller (SellerKey, SellerId, NickName, Name, GlancePage, LegalName, AuditKey)
VALUES (-1, NULL, NULL, NULL, NULL, NULL, -1)
GO
SET IDENTITY_INSERT DimSeller OFF
GO


/* Drop table DimSellerFeedback */
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DimSellerFeedback]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [DimSellerFeedback] 
GO

/* Create table DimSellerFeedback */
CREATE TABLE [DimSellerFeedback] (
   [SellerFeedbackKey]  int IDENTITY  NOT NULL
,  [Comment]  varchar(max)   NULL
,  [AuditKey]  int   NULL
, CONSTRAINT [PK_DimSellerFeedback] PRIMARY KEY CLUSTERED 
( [SellerFeedbackKey] )
) ON [PRIMARY]
GO


SET IDENTITY_INSERT DimSellerFeedback ON
GO
INSERT INTO DimSellerFeedback (SellerFeedbackKey, Comment, AuditKey)
VALUES (-1, NULL, -1)
GO
SET IDENTITY_INSERT DimSellerFeedback OFF
GO

/* Drop table FactSellersOffer */
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[FactSellersOffer]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [FactSellersOffer] 
GO

/* Create table FactSellersOffer */
CREATE TABLE [FactSellersOffer] (
   [OfferKey]  int   NOT NULL
,  [SellerKey]  int   NOT NULL
,  [BookKey]  int   NOT NULL
,  [Price]  decimal(7,2)   NOT NULL
,  [StartDateKey]  int   NOT NULL
,  [EndDateKey]  int   NOT NULL
,  [Quantity]  int   NOT NULL
,  [AuditKey]  int   NULL
) ON [PRIMARY]
GO



/* Drop table DimOffer */
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DimOffer]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [DimOffer] 
GO

/* Create table DimOffer */
CREATE TABLE [DimOffer] (
   [OfferKey]  int IDENTITY  NOT NULL
,  [ExchangeId]  char(19)   NULL
,  [ConditionNote]  varchar(255)   NULL
,  [Condition]  varchar(50)   NULL
,  [Availability]  varchar(50)   NULL
,  [IsEligibleForSuperSaverShipping]  char(1)  DEFAULT 'N' NOT NULL
,  [AuditKey]  int   NULL
, CONSTRAINT [PK_DimOffer] PRIMARY KEY CLUSTERED 
( [OfferKey] )
) ON [PRIMARY]
GO

SET IDENTITY_INSERT DimOffer ON
GO
INSERT INTO DimOffer (OfferKey, ExchangeId, ConditionNote, Condition, Availability, IsEligibleForSuperSaverShipping, AuditKey)
VALUES (-1, NULL, NULL, NULL, NULL, 'N', -1)
GO
SET IDENTITY_INSERT DimOffer OFF
GO


/* Drop table FactCustomerReviewRating */
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[FactCustomerReviewRating]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [FactCustomerReviewRating] 
GO

--Customer Reviews

/* Create table FactCustomerReviewRating */
CREATE TABLE [FactCustomerReviewRating] (
   [BookKey]  int   NOT NULL
,  [CustomerKey]  int   NOT NULL
,  [CustomerReviewKey]  int   NOT NULL
,  [TotalVotes]  int   NOT NULL
,  [HelpfulVotes]  int   NOT NULL
,  [Changed]  int   NOT NULL
,  [WasValidTill]  int  DEFAULT -1 NOT NULL
,  [RowIsCurrent]  char(1)   NOT NULL
,  [AuditKey]  int   NULL
) ON [PRIMARY]
GO



/* Drop table FactCustomerReview */
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[FactCustomerReview]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [FactCustomerReview] 
GO

/* Create table FactCustomerReview */
CREATE TABLE [FactCustomerReview] (
   [BookKey]  int   NOT NULL
,  [CustomerReviewKey]  int   NOT NULL
,  [CustomerKey]  int   NOT NULL
,  [Created]  int   NOT NULL
,  [CustomerRating]  tinyint   NOT NULL
,  [AuditKey]  int   NULL
) ON [PRIMARY]
GO

/* Drop table DimCustomerReview */
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DimCustomerReview]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [DimCustomerReview] 
GO

/* Create table DimCustomerReview */
CREATE TABLE [DimCustomerReview] (
   [CustomerReviewKey]  int IDENTITY  NOT NULL
,  [CustomerReviewId]  int   NULL
,  [Summary]  varchar(128)   NULL
,  [Content]  varchar(max)   NULL
,  [AuditKey]  int   NULL
, CONSTRAINT [PK_DimCustomerReview] PRIMARY KEY CLUSTERED 
( [CustomerReviewKey] )
) ON [PRIMARY]
GO

SET IDENTITY_INSERT DimCustomerReview ON
GO
INSERT INTO DimCustomerReview (CustomerReviewKey, CustomerReviewId, Summary, Content, AuditKey)
VALUES (-1, NULL, NULL, NULL, -1)
GO
SET IDENTITY_INSERT DimCustomerReview OFF
GO


/* Drop table DimCustomer */
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DimCustomer]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [DimCustomer] 
GO

/* Create table DimCustomer */
CREATE TABLE [DimCustomer] (
   [CustomerKey]  int IDENTITY  NOT NULL
,  [CustomerId]  char(15)   NULL
,  [Nickname]  varchar(255)   NULL
,  [BirthDay]  varchar(50)   NULL
,  [City]  varchar(255)   NULL
,  [State]  varchar(50)   NULL
,  [Country]  varchar(50)   NULL
,  [Location] varchar(255) NULL
,  [CustomerName]  varchar(255)   NULL
,  [RowIsCurrent]  char(1)   NULL
,  [RowStartDate]  datetime   NULL
,  [RowEndDate]  datetime  DEFAULT '9999-12-31' NULL
,  [RowChangeReason]  varchar(200)   NULL
,  [AuditKey]  int   NULL
, CONSTRAINT [PK_DimCustomer] PRIMARY KEY CLUSTERED 
( [CustomerKey] )
) ON [PRIMARY]
GO

SET IDENTITY_INSERT DimCustomer ON
GO
INSERT INTO DimCustomer (CustomerKey, CustomerId, Nickname, BirthDay, City, State, Country, CustomerName, RowIsCurrent, RowStartDate, RowEndDate, RowChangeReason, AuditKey)
VALUES (-1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Y', NULL, '9999-12-31', 'N/A', -1)
GO
SET IDENTITY_INSERT DimCustomer OFF
GO


--Book Details

/* Drop table FactSimiliarBook */
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[FactSimiliarBook]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [FactSimiliarBook] 
GO

/* Create table FactSimiliarBook */
CREATE TABLE [FactSimiliarBook] (
   [BookKey]  int NOT NULL
,  [SimiliarToBookKey]  int  NOT NULL
,  [AuditKey]  int   NULL
, CONSTRAINT [PK_FactSimiliarBook] PRIMARY KEY NONCLUSTERED 
( [BookKey], [SimiliarToBookKey] )
) ON [PRIMARY]
GO


/* Drop table DimAuthorToBook */
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DimAuthorToBook]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [DimAuthorToBook] 
GO

/* Create table DimAuthorToBook */
CREATE TABLE [DimAuthorToBook] (
   [AuthorKey]  int NOT NULL
,  [BookKey]  int  NOT NULL
,  [AuditKey]  int   NULL
, CONSTRAINT [PK_DimAuthorToBook] PRIMARY KEY CLUSTERED 
( [AuthorKey], [BookKey] )
) ON [PRIMARY]
GO




/* Drop table DimBook */
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DimBook]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [DimBook] 
GO

/* Create table DimBook */
CREATE TABLE [DimBook] (
   [BookKey]  int IDENTITY  NOT NULL
,  [Asin]  char(10)   NULL
,  [Binding]  varchar(50)   NULL
,  [DetailPageURL]  varchar(255)   NULL
,  [EAN]  char(13)   NULL
,  [ISBN]  char(13)   NULL
,  [Label]  varchar(50)   NULL
,  [Language]  varchar(50)   NULL
,  [Manufacturer]  varchar(50)   NULL
,  [NumberOfPages]  int   NULL
,  [PublicationDate]  smalldatetime   NULL
,  [Publisher]  varchar(50)   NULL
,  [Studio]  varchar(50)   NULL
,  [Title]  varchar(512)   NULL
,  [UPC]  char(12)   NULL
,  [Height]  decimal(7,2)   NULL
,  [Length]  decimal(7,2)   NULL
,  [Width]  decimal(7,2)   NULL
,  [Weight]  decimal(7,2)   NULL
,  [NumberOfItems]  int   NULL
,  [Price]  decimal(7,2)   NULL
,  [AuditKey]  int   NULL
, CONSTRAINT [PK_DimBook] PRIMARY KEY CLUSTERED 
( [BookKey] )
) ON [PRIMARY]
GO

SET IDENTITY_INSERT DimBook ON
GO
INSERT INTO DimBook (BookKey, Asin, Binding, DetailPageURL, EAN, ISBN, Label, Language, Manufacturer, NumberOfPages, PublicationDate, Publisher, Studio, Title, UPC, Height, Length, Width, Weight, NumberOfItems, Price,  AuditKey)
VALUES (-1, NULL, NULL, NULL, '0', '0', NULL, 'Unknown', 'Unknown', 0, '1901-01-01', 'Unknown', 'Unknown', 'Unknown', '-1', -1, -1, -1, -1, -1, -1, -1)
GO
SET IDENTITY_INSERT DimBook OFF
GO


/* Drop table DimAuthor */
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DimAuthor]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [DimAuthor] 
GO

/* Create table DimAuthor */
CREATE TABLE [DimAuthor] (
   [AuthorKey]  int IDENTITY  NOT NULL
,  [AuthorId]  int   NULL
,  [FullName]  varchar(50)   NULL
,  [AuditKey]  int   NULL
, CONSTRAINT [PK_DimAuthor] PRIMARY KEY CLUSTERED 
( [AuthorKey] )
) ON [PRIMARY]
GO


SET IDENTITY_INSERT DimAuthor ON
GO
INSERT INTO DimAuthor (AuthorKey, AuthorId, FullName, AuditKey)
VALUES (-1, NULL, NULL, -1)
GO
SET IDENTITY_INSERT DimAuthor OFF
GO



/* Drop table DimDate */
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DimDate]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [DimDate] 
GO

/* Create table DimDate */
CREATE TABLE [DimDate] (
   [DateKey]  int IDENTITY  NOT NULL
,  [FullDate]  datetime   NULL
,  [DayOfWeek]  tinyint   NULL
,  [DayOfMonth]  tinyint   NULL
,  [DayOfYear]  smallint   NULL
,  [MonthOfYear]  tinyint   NULL
,  [WeekdayWeekend]  char(7)   NULL
,  [DayNameOfWeek]  char(10)   NULL
,  [MonthName]  char(10)   NULL
,  [CalendarYear]  smallint   NULL
,  [AuditKey]  int   NULL
, CONSTRAINT [PK_DimDate] PRIMARY KEY CLUSTERED 
( [DateKey] )
) ON [PRIMARY]
GO

SET IDENTITY_INSERT DimDate ON
GO
INSERT INTO DimDate (DateKey, FullDate, DayOfWeek, DayOfMonth, DayOfYear, MonthOfYear, WeekdayWeekend, DayNameOfWeek, MonthName, CalendarYear, AuditKey)
VALUES (-1, NULL, NULL, NULL, NULL, NULL, 'Unknown', 'Unknown', 'Unknown', NULL, -1)
GO
SET IDENTITY_INSERT DimDate OFF
GO

/* Drop table DimDate */
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DimBrowseNode]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [DimBrowseNode] 
GO
/* Create table DimBrowseNode */
CREATE TABLE [DimBrowseNode] (
   [BrowseNodeKey]  int IDENTITY  NOT NULL
,  [BrowseNodeId]  int  NOT NULL
,  [Name]  varchar(255)   NOT NULL
,  [IsCategoryRoot]  char(1)   NULL
,  [ParentId]  int  NULL
,  [ParentBrowseNodeKey] int NULL
,  [AuditKey]  int   NULL
, CONSTRAINT [BrowseNodeKey] PRIMARY KEY CLUSTERED 
( [BrowseNodeKey] )
) ON [PRIMARY]
GO

SET IDENTITY_INSERT [DimBrowseNode] ON
GO
INSERT INTO [DimBrowseNode] ([BrowseNodeKey], [BrowseNodeId], [Name], [IsCategoryRoot], [ParentId], [AuditKey])
VALUES (-1, -1, 'unknown', NULL, NULL, -1)
GO
SET IDENTITY_INSERT [DimBrowseNode] OFF
GO

/* Drop table DimDate */
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DimBrowseNodeToBook]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [DimBrowseNodeToBook] 
GO
/* Create table [DimBrowseNodeToBook] */
CREATE TABLE [DimBrowseNodeToBook] (
   [BrowseNodeKey]  int NOT NULL
,  [BookKey]  int  NOT NULL
,  [AuditKey]  int   NULL
, CONSTRAINT [PK_DimBrowseNodeToBook] PRIMARY KEY CLUSTERED 
( [BrowseNodeKey], [BookKey] )
) ON [PRIMARY]
GO


ALTER TABLE AuditPkgExecution ADD CONSTRAINT
   FK_AuditPkgExecution_ParentPkgExecKey FOREIGN KEY
   (
   ParentPkgExecKey
   ) REFERENCES AuditPkgExecution
   ( PkgExecKey )
     ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
GO
 
ALTER TABLE AuditTableProcessing ADD CONSTRAINT
   FK_AuditTableProcessing_PkgExecKey FOREIGN KEY
   (
   PkgExecKey
   ) REFERENCES AuditPkgExecution
   ( PkgExecKey )
     ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
GO
 
ALTER TABLE DimAudit ADD CONSTRAINT
   FK_DimAudit_TableProcessKey FOREIGN KEY
   (
   TableProcessKey
   ) REFERENCES AuditTableProcessing
   ( TableProcessKey )
     ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
GO
 
ALTER TABLE FactListItem ADD CONSTRAINT
   FK_FactListItem_ListKey FOREIGN KEY
   (
   ListKey
   ) REFERENCES DimList
   ( ListKey )
     ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
GO
 
ALTER TABLE FactListItem ADD CONSTRAINT
   FK_FactListItem_BookKey FOREIGN KEY
   (
   BookKey
   ) REFERENCES DimBook
   ( BookKey )
     ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
GO
 
ALTER TABLE FactListItem ADD CONSTRAINT
   FK_FactListItem_DateAddedKey FOREIGN KEY
   (
   DateAddedKey
   ) REFERENCES DimDate
   ( DateKey )
     ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
GO
 
ALTER TABLE FactList ADD CONSTRAINT
   FK_FactList_ListKey FOREIGN KEY
   (
   ListKey
   ) REFERENCES DimList
   ( ListKey )
     ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
GO
 
ALTER TABLE FactList ADD CONSTRAINT
   FK_FactList_CustomerKey FOREIGN KEY
   (
   CustomerKey
   ) REFERENCES DimCustomer
   ( CustomerKey )
     ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
GO
 
ALTER TABLE FactList ADD CONSTRAINT
   FK_FactList_CreatedKey FOREIGN KEY
   (
   CreatedKey
   ) REFERENCES DimDate
   ( DateKey )
     ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
GO
 
ALTER TABLE FactList ADD CONSTRAINT
   FK_FactList_UpdatedKey FOREIGN KEY
   (
   UpdatedKey
   ) REFERENCES DimDate
   ( DateKey )
     ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
GO

ALTER TABLE DimAuthorToBook ADD CONSTRAINT
   FK_AuthorToAuthorToBook FOREIGN KEY
   (
   AuthorKey
   ) REFERENCES DimAuthor
   ( AuthorKey )
     ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
GO

ALTER TABLE DimAuthorToBook ADD CONSTRAINT
   FK_BookToAuthorToBook FOREIGN KEY
   (
   BookKey
   ) REFERENCES DimBook
   ( BookKey )
     ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
GO

ALTER TABLE FactSimiliarBook ADD CONSTRAINT
   FK_FactSimilairBookTo FOREIGN KEY
   (
   SimiliarToBookKey
   ) REFERENCES DimBook
   ( BookKey )
     ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
GO

ALTER TABLE FactSimiliarBook ADD CONSTRAINT
   FK_FactSimilairBook FOREIGN KEY
   (
   BookKey
   ) REFERENCES DimBook
   ( BookKey )
     ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
GO
 
ALTER TABLE FactSellersOffer ADD CONSTRAINT
   FK_FactSellersOffer_OfferKey FOREIGN KEY
   (
   OfferKey
   ) REFERENCES DimOffer
   ( OfferKey )
     ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
GO
 
ALTER TABLE FactSellersOffer ADD CONSTRAINT
   FK_FactSellersOffer_SellerKey FOREIGN KEY
   (
   SellerKey
   ) REFERENCES DimSeller
   ( SellerKey )
     ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
GO
 
ALTER TABLE FactSellersOffer ADD CONSTRAINT
   FK_FactSellersOffer_BookKey FOREIGN KEY
   (
   BookKey
   ) REFERENCES DimBook
   ( BookKey )
     ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
GO
 
ALTER TABLE FactSellersOffer ADD CONSTRAINT
   FK_FactSellersOffer_StartDateKey FOREIGN KEY
   (
   StartDateKey
   ) REFERENCES DimDate
   ( DateKey )
     ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
GO
 
ALTER TABLE FactSellersOffer ADD CONSTRAINT
   FK_FactSellersOffer_EndDateKey FOREIGN KEY
   (
   EndDateKey
   ) REFERENCES DimDate
   ( DateKey )
     ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
GO
 
ALTER TABLE FactCustomerReviewRating ADD CONSTRAINT
   FK_FactCustomerReviewRating_BookKey FOREIGN KEY
   (
   BookKey
   ) REFERENCES DimBook
   ( BookKey )
     ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
GO
 
ALTER TABLE FactCustomerReviewRating ADD CONSTRAINT
   FK_FactCustomerReviewRating_CustomerKey FOREIGN KEY
   (
   CustomerKey
   ) REFERENCES DimCustomer
   ( CustomerKey )
     ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
GO
 
ALTER TABLE FactCustomerReviewRating ADD CONSTRAINT
   FK_FactCustomerReviewRating_CustomerReviewKey FOREIGN KEY
   (
   CustomerReviewKey
   ) REFERENCES DimCustomerReview
   ( CustomerReviewKey )
     ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
GO
 
ALTER TABLE FactCustomerReviewRating ADD CONSTRAINT
   FK_FactCustomerReviewRating_Changed FOREIGN KEY
   (
   Changed
   ) REFERENCES DimDate
   ( DateKey )
     ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
GO
 
ALTER TABLE FactCustomerReview ADD CONSTRAINT
   FK_FactCustomerReview_BookKey FOREIGN KEY
   (
   BookKey
   ) REFERENCES DimBook
   ( BookKey )
     ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
GO
 
ALTER TABLE FactCustomerReview ADD CONSTRAINT
   FK_FactCustomerReview_CustomerReviewKey FOREIGN KEY
   (
   CustomerReviewKey
   ) REFERENCES DimCustomerReview
   ( CustomerReviewKey )
     ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
GO
 
ALTER TABLE FactCustomerReview ADD CONSTRAINT
   FK_FactCustomerReview_CustomerKey FOREIGN KEY
   (
   CustomerKey
   ) REFERENCES DimCustomer
   ( CustomerKey )
     ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
GO
 
ALTER TABLE FactCustomerReview ADD CONSTRAINT
   FK_FactCustomerReview_Created FOREIGN KEY
   (
   Created
   ) REFERENCES DimDate
   ( DateKey )
     ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
GO

ALTER TABLE FactCustomerReviewRating ADD CONSTRAINT
   FK_FactCustomerReviewRating_WasValidTill FOREIGN KEY
   (
   wasValidTill
   ) REFERENCES DimDate
   ( DateKey )
     ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
GO
 
ALTER TABLE FactSellerFeedback ADD CONSTRAINT
   FK_FactSellerFeedback_SellerFeedbackKey FOREIGN KEY
   (
   SellerFeedbackKey
   ) REFERENCES DimSellerFeedback
   ( SellerFeedbackKey )
     ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
GO
 
ALTER TABLE FactSellerFeedback ADD CONSTRAINT
   FK_FactSellerFeedback_SellerKey FOREIGN KEY
   (
   SellerKey
   ) REFERENCES DimSeller
   ( SellerKey )
     ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
GO
 
ALTER TABLE FactSellerFeedback ADD CONSTRAINT
   FK_FactSellerFeedback_RatedByKey FOREIGN KEY
   (
   RatedByKey
   ) REFERENCES DimCustomer
   ( CustomerKey )
     ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
GO
 
ALTER TABLE FactSellerFeedback ADD CONSTRAINT
   FK_FactSellerFeedback_CreatedDateKey FOREIGN KEY
   (
   CreatedDateKey
   ) REFERENCES DimDate
   ( DateKey )
     ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
GO

ALTER TABLE DimBrowseNode ADD CONSTRAINT
   FK_BrowseNode_ParentBrowseNode FOREIGN KEY
   (
   ParentBrowseNodeKey
   ) REFERENCES DimBrowseNode
   ( BrowseNodeKey )
     ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
GO

ALTER TABLE DimBrowseNodeToBook ADD CONSTRAINT
   FK_BrowseNodeToBook_BrowseNode FOREIGN KEY
   (
   BrowseNodeKey
   ) REFERENCES DimBrowseNode
   ( BrowseNodeKey )
     ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
GO

ALTER TABLE DimBrowseNodeToBook ADD CONSTRAINT
   FK_BrowseNodeToBook_Book FOREIGN KEY
   (
   BookKey
   ) REFERENCES DimBook
   ( BookKey )
     ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
GO
 
CREATE PROCEDURE [dbo].[Insert_AuditPkgExecution]
     @PkgName varchar(50) ,
     @PkgGUID uniqueidentifier ,
     @PkgVersionGUID uniqueidentifier,
     @PkgVersionMajor smallint,
     @PkgVersionMinor smallint,
     @ExecStartDT datetime,
     @ParentPkgExecKey int
AS
BEGIN
     SET NOCOUNT ON;

     IF @ParentPkgExecKey = 0 SET @ParentPkgExecKey = NULL

     INSERT INTO AuditPkgExecution (
             PkgName, PkgGUID, PkgVersionGUID, PkgVersionMajor,
             PkgVersionMinor, ExecStartDT, ParentPkgExecKey)
     VALUES (@PkgName, @PkgGUID, @PkgVersionGUID, @PkgVersionMajor,
             @PkgVersionMinor, @ExecStartDT, @ParentPkgExecKey)
END
